package com.yunling.qishi.macaca.utils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * excel相关操作，增删改查等，基于jxl，excel版本限于2003-2007版。行数和列数均从0开始
 * @author yunling.he
 *1.读取一条数据
 *2.读取一行数据
 *3.读取全部数据
 *4.写一条数据
 *5.写一行数据
 *6.写全部数据
 *7.判断文件格式、地址等是否正确，文件是否存在
 */
public class ExcelUtil {
	private Workbook workbook;
	private Sheet sheet;
	private WritableWorkbook writableWorkbook;
	private WritableSheet writableSheet;
	private File file;
	private int rows;
	private int columns;
	public ExcelUtil(String filepath) throws BiffException, IOException {
		this.file=new File(filepath);
		isExist(file);
		//System.out.println(file);
		workbook=Workbook.getWorkbook(file);
		sheet=workbook.getSheet(0);
		rows=sheet.getRows();
		columns=sheet.getColumns();
	}
	/**
	 * 读取excel一个数据
	 * @param row  第几行，从0开始
	 * @param column 第几列，从0开始
	 * @return String 返回所查询的内容
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readCell(int row,int column) throws BiffException, IOException{
		Cell cell=sheet.getCell(column, row);
		return cell.getContents();
	}
	/**
	 * 读取一行数据
	 * @param row
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	 
	public List<String> readRow(int row) throws BiffException, IOException{
		List<String> list=new ArrayList<>();		
		for(int i=0;i<columns;i++){
			Cell cell = sheet.getCell(i, row);
			list.add(cell.getContents());
		}	
		return list;
		
	}
	/**
	 * 读取一列数据
	 */
	public List<String> readColumn(int column) throws BiffException, IOException{
		List<String> list=new ArrayList<>();
		for(int i=0;i<rows;i++){
			Cell cell = sheet.getCell(column, i);
			list.add(cell.getContents());
		}	
		return list;
		
	}
	/**
	 * 读取全部数据
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public List<List<String>> readAll() throws BiffException, IOException{
		List<List<String>> list=new ArrayList<>();
		for(int i=0;i<rows;i++){
			List<String> readRow = readRow(i);
			list.add(readRow);
		}
		return list;
	}
	/**
	 * 写入一个数据
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public void writeCell(int column,int row ,String content) throws IOException, RowsExceededException, WriteException{
		writableWorkbook=Workbook.createWorkbook(file, workbook);
		writableSheet=writableWorkbook.getSheet(0);
		Label label=new Label(column, row, content);
		writableSheet.addCell(label);
		writableWorkbook.write();
		writableWorkbook.close();
	}
	/**
	 * 写入一行数据
	 */
	public void writeRow(int row ,List<String> content) throws IOException, RowsExceededException, WriteException{
		writableWorkbook=Workbook.createWorkbook(file, workbook);
		writableSheet=writableWorkbook.getSheet(0);
		int size = content.size();
		for (int i=0;i<size;i++) {
			Label label=new Label(i, row, content.get(i));
			writableSheet.addCell(label);
		}
		writableWorkbook.write();
		writableWorkbook.close();
	}
	/**
	 * 写入一列数据
	 */
	public void writeColumn(int column ,List<String> content) throws IOException, RowsExceededException, WriteException{
		writableWorkbook=Workbook.createWorkbook(file, workbook);
		writableSheet=writableWorkbook.getSheet(0);
		int size = content.size();
		for (int i=0;i<size;i++) {
			Label label=new Label(column, i, content.get(i));
			writableSheet.addCell(label);
		}
		writableWorkbook.write();
		writableWorkbook.close();
	}
	/**
	 * 写入全部数据
	 */
	public void writeColumn(List<List<String>> content) throws IOException, RowsExceededException, WriteException{
		writableWorkbook=Workbook.createWorkbook(file, workbook);
		writableSheet=writableWorkbook.getSheet(0);
		int row = content.size();
		for (int i=0;i<row;i++) {
			int column = content.get(i).size();
			for(int j=0;j<column;j++){
				Label label=new Label(j, i, content.get(i).get(j));
				writableSheet.addCell(label);
				
			}
		}
		writableWorkbook.write();
		writableWorkbook.close();
	}
	
	
	
	
	
	
	
	/**
	 * 判断文件是否存在，存在刚返回true,不存在创建并返回false，
	 * ps:未判断是否为excel文件
	 * @param file
	 * @return
	 */
	private boolean isExist(File file) {
		File parentFile = file.getParentFile();
		if(file.exists()&&file.isFile())return true;
		if(!(parentFile.exists()&&parentFile.isDirectory()))parentFile.mkdirs();
		try {
			Log.info("创建文件："+file.getName());
			file.createNewFile();
		} catch (IOException e) {
			Log.error("创建文件失败！"+file.getName());
			e.printStackTrace();
		}
		return false;
	}

}





















